package com.szwistar.common.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.support.DatabaseConnection;
import com.szwistar.common.datastruct.Utils;

public class DbUtils {
	static Logger LOG = LoggerFactory.getLogger(DbUtils.class);

	/**
	 * 执行 SQL 文件<br>
	 * ！！！注意：SQL文件如果含有中文，就必须使用 UTF-8 (无BOM) 格式保存！否则会出现不可预测的错误！！！
	 * @param cs
	 * @param sqlFile SQL 文件
	 * @throws Exception
	 */
	public static void execSqlFile(ConnectionSource cs, File sqlFile) throws Exception {
		FileInputStream in = new FileInputStream(sqlFile);

		BufferedReader br = new BufferedReader(new InputStreamReader(in, "UTF-8"));
		String line = null;
        StringBuffer strBuffer = new StringBuffer();
        while( (line = br.readLine()) != null ) {
        	line = line.replaceAll("--.*", ""); // 去除单行注释
        	if(Utils.isNotEmpty(line)) {
        		strBuffer.append(line + "\n");
        	}
        }
        br.close();
        in.close();

        DatabaseConnection dbConn = cs.getReadWriteConnection(null);

    	try {
            line = strBuffer.toString();
	        String[] sqlArray = line.split(";");
	        for(String sql: sqlArray) {
	        	sql = sql.trim();
	        	if( !sql.equals("") ) {
	            	//LOG.debug(sql);
                    line = sql;
	            	dbConn.executeStatement(sql, DatabaseConnection.DEFAULT_RESULT_FLAGS);
	        	}
	        }
    	} catch(Exception e) {
    	    LOG.error("执行SQL语句失败：\n{}", line);
            throw e;
    	} finally {
            // 释放资源
            cs.releaseConnection(dbConn);
    	}
	}

	public void execSql(ConnectionSource cs, String sqls) throws Exception {
        DatabaseConnection dbConn = cs.getReadWriteConnection(null);
        String[] sqlArray = sqls.split(";");

        for( String sql : sqlArray ) {
        	if( !Utils.isEmpty(sql) ) {
            	LOG.debug(sql);
            	dbConn.executeStatement(sql, DatabaseConnection.DEFAULT_RESULT_FLAGS);
        	}
        }

    	dbConn.close();
	}

	public static Object get1Row(ConnectionSource cs, String sql) throws Exception {
        DatabaseConnection dbConn = cs.getReadWriteConnection(null);
    	if( !Utils.isEmpty(sql) ) {
        	LOG.debug("get1Row() for: {}", sql);
        	Object ret = dbConn.queryForOne(sql, null, null, null, null);
        	dbConn.close();
        	return ret;
    	}
    	return null;
	}

	public static class Jsoner {
		GenericRawResults<String[]> res;

		protected Jsoner(GenericRawResults<String[]> res) {
			this.res = res;
		}

		public static Jsoner wrap(GenericRawResults<String[]> res) {
			return new Jsoner(res);
		}

		/**
		 * 转化为JSON二维数组，第一行为字段名
		 */
		public String to2DArray() {
			return null;
		}

		/**
		 * 转化为对象数组，每一行为一个对象，对象字段名为列名
		 */
		public String toObjects() {
			return null;
		}
	}

	public static Jsoner jsoner(GenericRawResults<String[]> res) {
		return Jsoner.wrap(res);
	}
}
